package Fisica;

import java.io.File;
import java.io.FileOutputStream;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import Logica.Util;
import Modelo.Tarjeta;
import Modelo.TarjetaCompra;

public class DAOCompra {

	public static String SELECT = "select * from tarjeta_compra";	
	public static String FIRST = "SELECT * FROM tarjeta_compra order by id asc;";
	public static String LAST = "SELECT * FROM tarjeta_compra order by id desc;";
	public static String PRE = "SELECT * FROM tarjeta_compra WHERE id < ? order by id desc";
	public static String POST = "SELECT * FROM tarjeta_compra WHERE id > ? order by id asc";
	public static String INSERT = "INSERT INTO tarjeta_compra (nro_ticket, puntos, id_tarjeta, obs, fecha) VALUES (?,?,?,?,?);";
	
	
	public static Modelo.TarjetaCompra get (int id){
		String sql = SELECT + " where id = ?;";
		try {
			PreparedStatement st = Conexion.getConexion().prepareStatement(sql);	
			st.setInt(1, id);
			ResultSet rs = st.executeQuery();			
			
			if (rs.next()){
				Modelo.TarjetaCompra a = new Modelo.TarjetaCompra(); 
				a.setId(rs.getInt("id")); 	
				a.setNro_ticket(rs.getString("nro_ticket"));
				a.setPuntos(rs.getInt("puntos"));
				a.setNro_tarjeta(rs.getInt("id_tarjeta"));
				a.setObs(rs.getString("obs"));
				a.setFecha(Util.getDate(rs.getString("fecha")));
				
				return a;
			}			
		} catch (SQLException e) {
			System.out.print("ERROR EN LA RECUPERACION DE DATOS DE LA BASE");
			e.printStackTrace();
		} 				
		return new TarjetaCompra();
	}
	
	
	public static List<Object> getList (){
		List<Object> list = new ArrayList();
		String fecha;
		String sql = SELECT +";";
		try {
			PreparedStatement st = Conexion.getConexion().prepareStatement(sql);	
			ResultSet rs = st.executeQuery();			
			TarjetaCompra a;
			while (rs.next()){	
				fecha = "";
				a = new Modelo.TarjetaCompra(); 
				a.setId(rs.getInt("id")); 	
				a.setNro_ticket(rs.getString("nro_ticket"));
				a.setPuntos(rs.getInt("puntos"));
				a.setNro_tarjeta(rs.getInt("id_tarjeta"));
				a.setObs(rs.getString("obs"));
				if (!rs.getString("fecha").isEmpty()){
					fecha = rs.getString("fecha");
				}
				a.setFecha(Util.getDate(fecha));
				
				list.add(a);
			}			
		} catch (SQLException e) {
			System.out.print("ERROR EN LA RECUPERACION DE DATOS DE LA BASE");
			e.printStackTrace();
		} 				
		return list;
	}
	
	 public boolean insert(Object obj) { //ok
			TarjetaCompra v = (TarjetaCompra)obj;
//			INSERT INTO tarjeta_compra (nro_ticket, puntos, id_tarjeta, obs) VALUES ('12654',12,12345,'observacion 1');
			try {			
				PreparedStatement st;
				ResultSet rs;
				//INSERTO
				st = Conexion.getConexion().prepareStatement(INSERT);	
				st.setString(1, v.getNro_ticket());
				st.setInt(2, v.getPuntos());
				st.setInt(3, v.getNro_tarjeta());
				st.setString(4, v.getObs());
				st.setString(5, Util.getDate(v.getFecha()));
				
				st.executeUpdate();
				//RECUPERO
				st = Conexion.getConexion().prepareStatement(SELECT + " where nro_ticket = ? and id_tarjeta = ? and fecha = ?;");
				st.setString(1, v.getNro_ticket());
				st.setInt(2, v.getNro_tarjeta());
				st.setString(3, Util.getDate(v.getFecha()));
				
				rs = st.executeQuery();
				if(rs.next())
					v.setId(rs.getInt("id"));
				System.out.println("inserto: "+v.getId());

				return true;
				
			}catch(Exception e){
				System.out.println("exception al intentar insertar: "+v.getId());
				e.printStackTrace();
			}
			return false;				
		}
	 
	 public Object getObject (PreparedStatement st){ //ok
			TarjetaCompra a = new TarjetaCompra(); 	
			
			try {
				ResultSet rs = st.executeQuery();			
				FileOutputStream archivofoto;
				File file; 
				if (rs.next()){
					a.setId(rs.getInt("id"));
					a.setId(rs.getInt("id")); 	
					a.setNro_ticket(rs.getString("nro_ticket"));
					a.setPuntos(rs.getInt("puntos"));
					a.setNro_tarjeta(rs.getInt("id_tarjeta"));
					a.setObs(rs.getString("obs"));
					a.setFecha(Util.getDate(rs.getString("fecha")));
					
				}	
				return a;
			} catch (SQLException e) {
				System.out.print("ERROR EN LA RECUPERACION DE DATOS DE LA BASE");
				e.printStackTrace();
			} 		
			return new Tarjeta();		
		}

		public Object getPre(int id) {
			try {
				PreparedStatement st = Conexion.getConexion().prepareStatement(PRE);
				st.setInt(1, id);
				return getObject(st);
			} catch (SQLException e) {
				
				e.printStackTrace();
			}
			
			return null;
		}

		public Object getPost(int id) {
			try {
				PreparedStatement st = Conexion.getConexion().prepareStatement(POST);
				st.setInt(1, id);
				return getObject(st);
			} catch (SQLException e) {			
				e.printStackTrace();
			}		
			return null;
		}
		
		public Object getFirst() {
			try {
				PreparedStatement st = Conexion.getConexion().prepareStatement(FIRST);
				return getObject(st);
			} catch (SQLException e) {			
				e.printStackTrace();
			}		
			return null;
		}
		
		public Object getLast() {
			try {
				PreparedStatement st = Conexion.getConexion().prepareStatement(LAST);
				return getObject(st);			
			} catch (SQLException e) {			
				e.printStackTrace();
			}		
			return null;
		}
	 
}
